﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Gencode.Gengui.Wizzard;
using Gencode.Common.ODBC;
using System.Data.Odbc;
using Gencode.SQLWizz.DataType;

namespace Gencode.SQLWizz.Controls
{
	public partial class Logon : WizzardControl
	{
		public Logon(string previousControl, string nextControl, WizzardDialog controler)
			: base(previousControl, nextControl, controler)
		{
			InitializeComponent();
			InitializeODBC();

            SetCheck();
		}

		private void InitializeODBC()
		{
            try
            {
                for (int i = 0; i < ODBCManager.GetUserDSNList().Length; i++)
                {
                    // Nur SQL Server in die Liste einbinden
                    if (ODBCManager.GetUserDSNList()[i].GetDSNDriverName().ToString() == "SQL Server")
                        listODBC.Items.Add(ODBCManager.GetUserDSNList()[i].ToString());
                }
            }
            catch (Exception)
            {
                Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.ODBC_ERROR,
                    "Keine USER-DSN-Verbindung vorhanden!");
                //Gencode.Common.Util.ErrorHandler.LoggError("Logon Error", "Logon()", "Kein DSN vorhanden. \n" + e.Message);
                //Gencode.Common.Util.ErrorHandler.ShowError("Es ist ein Fehler beim Verbindungsaufbau entstanden.\nDer Fehler ist beim Aufbau der DSN-Verbindung aufgetreten.\nBitte überprüfen Sie die Datenbankverbindung oder kontaktieren Sie ihren Administrator.", "Anmeldefehler");
            }
            try{
				for (int i = 0; i < ODBCManager.GetSystemDSNList().Length; i++)
				{
					if (ODBCManager.GetSystemDSNList()[i].GetDSNDriverName().ToString() == "SQL Server")
						listODBC.Items.Add(ODBCManager.GetSystemDSNList()[i].ToString());
				}
			}
			catch (Exception)
			{
				Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.ODBC_ERROR,
					"Keine SYSTEM-DSN-Verbindung vorhanden!");
				//Gencode.Common.Util.ErrorHandler.LoggError("Logon Error", "Logon()", "Kein DSN vorhanden. \n" + e.Message);
                //Gencode.Common.Util.ErrorHandler.ShowError("Es ist ein Fehler beim Verbindungsaufbau entstanden.\nDer Fehler ist beim Aufbau der DSN-Verbindung aufgetreten.\nBitte überprüfen Sie die Datenbankverbindung oder kontaktieren Sie ihren Administrator.", "Anmeldefehler");
			}
		}

		public override bool MoveNext()
		{
            if (checkNewDB.Checked)
            {
                if (ValidateConection(true))
                {
                    LogonData logondata = new LogonData();
                    logondata.ODBC = listODBC.Items[listODBC.SelectedIndex].ToString();
                    logondata.User = txtUser.Text;
                    logondata.Password = txtPwd.Text;

                    Controler.DataObjects.AddType(logondata, typeof(LogonData));

					nextControl = "newdatabase";

                    return true;
                }
                else
                    MessageBox.Show("ODBC konnte nicht angemeldet werden\noder User ist nicht korrekt.", "Kein ODBC", MessageBoxButtons.OK);
            }
            else
            {
                if (ValidateConection(false))
                {
                    LogonData logondata = new LogonData();
                    logondata.ODBC = listODBC.Items[listODBC.SelectedIndex].ToString();
                    logondata.User = txtUser.Text;
                    logondata.Password = txtPwd.Text;
                    logondata.Database = comboDatabse.Text;

                    Controler.DataObjects.AddType(logondata, typeof(LogonData));

					nextControl = "upddatabase";

                    return true;
                }
                else
                    MessageBox.Show("ODBC konnte nicht angemeldet werden\noder keine Datenbank vorhanden.", "Kein ODBC", MessageBoxButtons.OK);
            }

			return false;
		}

		private bool ValidateConection(bool newDatabase)
		{
            if (newDatabase)
            {
				if (CheckDatabaseUser())
					return true;
            }
            else
            {
                if (comboDatabse.SelectedIndex >= 0)
                    return true;
            }

			return false;
		}

		private bool RetriveDatabases()
		{
			string strSQL = "sp_helpdb";
			string odbcStrConnect = "DSN=" + listODBC.Items[listODBC.SelectedIndex].ToString() + ";UID=" + txtUser.Text + ";PWD=" + txtPwd.Text + ";Database=master";
			OdbcConnection odbcConn = new OdbcConnection(odbcStrConnect);

			DataSet ds = new DataSet();
			OdbcDataAdapter cmd = new OdbcDataAdapter(strSQL, odbcConn);
			try
			{
				cmd.Fill(ds, "dbs");
				odbcConn.Close();
			}
			catch (OdbcException exp)
			{
				Gencode.Common.Util.ErrorHandler.LoggError("SQLWizz",exp);
				return false;
			}
			finally
			{
				comboDatabse.Items.Clear();
				comboDatabse.Text = string.Empty;

				DataView dv = new DataView(ds.Tables["dbs"]);
				for (int i = 0; i < dv.Count; i++)
				{
					// Keine System-Datenbanken laden
					if (dv[i].Row[0].ToString() != "master" && dv[i].Row[0].ToString() != "model" && dv[i].Row[0].ToString() != "msdb" && dv[i].Row[0].ToString() != "tempdb")
						if (TestConnection(listODBC.Items[listODBC.SelectedIndex].ToString(), txtUser.Text, txtPwd.Text, dv[i].Row[0].ToString()))
						{
							comboDatabse.Items.Add(dv[i].Row[0].ToString());
						}
				}
			}

			return true;
		}
		
		/// <summary>
		/// returns true if the user is a database adminuser
		/// </summary>
		/// <returns></returns>
		private bool CheckDatabaseUser()
		{
			bool retVal = false;
			string sqlUsrStr = "select * from sys.server_principals u " +
				"inner join sys.server_role_members m on m.member_principal_id = u.principal_id " +
				"inner join sys.server_principals g on g.principal_id = m.role_principal_id and g.type = 'R' and g.name in ('sysadmin','serveradmin') " +
				"where u.type= 'S' and u.is_disabled = 0 and u.name = '" + txtUser.Text + "'";

			if (listODBC.SelectedIndex < 0)
				return false;
			string odbcStrConnect = "DSN=" + listODBC.Items[listODBC.SelectedIndex].ToString() + ";UID=" + txtUser.Text + ";PWD=" + txtPwd.Text + ";Database=master";
			OdbcConnection odbcConn = new OdbcConnection(odbcStrConnect);

			DataSet ds = new DataSet();
			OdbcDataAdapter cmd = new OdbcDataAdapter(sqlUsrStr, odbcConn);
			try
			{
				cmd.Fill(ds, "dbs");
				odbcConn.Close();
			}
			catch (OdbcException exp)
			{
				Gencode.Common.Util.ErrorHandler.LoggError("SQLWizz", exp);
				return false;
			}
			finally
			{
				comboDatabse.Items.Clear();
				comboDatabse.Text = string.Empty;

				DataView dv = new DataView(ds.Tables["dbs"]);
				if (dv.Count > 0)
					retVal = true;
			}

			return retVal;
		}

		public bool TestConnection(string odbc, string user, string pwd, string database)
		{
			bool connectionOK = true;

			//New with Gencode.Common
			Gencode.Common.SQL.DatabaseConection.ODBCConnetionString = "DSN=" + odbc +
				";UID=" + user +
				";PWD=" + pwd +
				";Database=" + database + ";";
			Gencode.Common.SQL.DatabaseConection.ODBCConnection = new OdbcConnection(Gencode.Common.SQL.DatabaseConection.ODBCConnetionString);

			try
			{
				//DataView dv = Gencode.Common.SQL.SimpleQuerry.ExecuteForDataView("select 1 from sysobjects where name = 'PrsVerwaltung'and type = 'U'");
				DataView dv = Gencode.Common.SQL.SimpleQuerry.ExecuteForDataView("select 1 from sysobjects where type = 'U'");
				if (dv.Count > 0)
					connectionOK = true;
				else
					connectionOK = false;
			}
			catch (OdbcException)
			{
				connectionOK = false;
			}

			return connectionOK;
		}

        private void SetCheck()
        {
            if (checkNewDB.Checked)
            {
                comboDatabse.Enabled = false;
            }
            else
            {
                comboDatabse.Enabled = true;
            }
        }

		private void comboDatabse_DropDown(object sender, EventArgs e)
		{
			if (listODBC.SelectedIndex >= 0 && txtUser.Text != string.Empty)
			{
				RetriveDatabases();
			}
			else
				MessageBox.Show("ODBC konnte nicht angemeldet werden\noder keine Datenbank vorhanden.", "Kein ODBC", MessageBoxButtons.OK);
		}

        private void checkNewDB_CheckedChanged(object sender, EventArgs e)
        {
            SetCheck();
        }
	}
}
